package com.gmail.liulijuan.DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;

public class EmployeeDao {
	public Employee findByID(int id) {
		ResultSet rs = null;
		Employee el = null;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM Employee where ID=" + id);
			el = new Employee();
			while (rs.next()) {

				el.setAge(rs.getInt("Age"));
				el.setEmployeeName(rs.getString("EmployeeName"));
				el.setID(rs.getInt("ID"));
				el.setPassword(rs.getString("Password"));
				el.setPhoneNumber(rs.getString("PhoneNumber"));
				el.setRole(rs.getString("Role"));
				el.setSex(rs.getString("Sex"));
			}

			rs.close();

			stmt.close();
			conn.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return el;
	}

	public ArrayList getAllUsers() {
		ResultSet rs = null;
		Employee el = null;
		ArrayList useral = new ArrayList();
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM Employee");

			while (rs.next()) {
				el = new Employee();
				el.setAge(rs.getInt("Age"));
				el.setEmployeeName(rs.getString("EmployeeName"));
				el.setID(rs.getInt("ID"));
				el.setPassword(rs.getString("Password"));
				el.setPhoneNumber(rs.getString("PhoneNumber"));
				el.setRole(rs.getString("Role"));
				el.setSex(rs.getString("Sex"));
				useral.add(el);
			}

			rs.close();

			stmt.close();
			conn.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return useral;
	}
	
	public int InsertEmployee(Employee em){
		int result=0;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();

			String sql="insert into Employee(EmployeeName,Password,Sex,Age,Role,Phonenumber) values('"
				+em.getEmployeeName()
				+"','"
				+em.getPassword()
				+"','"
				+em.getSex()
				+"',"
				+em.getAge()
				+",'"
				+em.getRole()
				+"','"
				+em.getPhoneNumber()
				+"')";
			PreparedStatement ps = conn
					.prepareStatement(sql);

			result=ps.executeUpdate();

			ps.close();

			stmt.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}
	
	public int UpdataEmployee(Employee em){
		int result=0;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();

			String sql="update Employee set EmployeeName=?,Password=?,Sex=?,Age=?,Role=?,Phonenumber=? where id=? ";
			PreparedStatement ps = conn
					.prepareStatement(sql);
			ps.setString(1,em.EmployeeName);
			ps.setString(2,em.Password);
			ps.setString(3,em.Sex);
			ps.setInt(4,em.Age);
			ps.setString(5,em.Role);
			ps.setString(6,em.PhoneNumber);
			ps.setInt(7,em.ID);

			result=ps.executeUpdate();

			ps.close();

			stmt.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}

}
